/*
 Navicat Premium Dump SQL

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44)
 Source Host           : 127.0.0.1:3306
 Source Schema         : smart_community

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44)
 File Encoding         : 65001

 Date: 15/08/2025 14:14:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc_billing_method
-- ----------------------------
DROP TABLE IF EXISTS `sc_billing_method`;
CREATE TABLE `sc_billing_method` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '计费方式ID',
  `property_company_id` bigint(20) NOT NULL COMMENT '物业公司ID',
  `building_type_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '建筑类型',
  `fee_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '费用类型：property-物业费，sanitation-卫生费，gas-燃气费，water-水费，electricity-电费',
  `method_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '计费方式名称',
  `method_type` tinyint(1) NOT NULL COMMENT '计费类型：0-按面积计费，1-固定费用，2-阶梯计费，3-按表计费，4-阶梯电价',
  `unit_price` decimal(10,2) DEFAULT NULL COMMENT '单价（元/㎡或元/m³或元/kWh等）',
  `fixed_amount` decimal(10,2) DEFAULT NULL COMMENT '固定金额（元）',
  `tier_config` json DEFAULT NULL COMMENT '阶梯配置JSON格式',
  `description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '计费方式描述',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：0-禁用，1-启用',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除：0-否，1-是',
  `version` int(11) DEFAULT '1' COMMENT '版本号',
  `remark` text COLLATE utf8mb4_unicode_ci COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_property_company_id` (`property_company_id`),
  KEY `idx_fee_type` (`fee_type`),
  KEY `idx_method_type` (`method_type`),
  KEY `idx_status` (`status`),
  KEY `idx_sort_order` (`sort_order`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='计费方式表';

-- ----------------------------
-- Records of sc_billing_method
-- ----------------------------
BEGIN;
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (1, 1, '', 'property', '按面积计费', 0, 2.50, NULL, NULL, '根据房屋面积和单价计算费用', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '物业费按面积计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (2, 1, '', 'property', '固定费用', 1, NULL, 200.00, NULL, '每户固定收取费用', 1, 2, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '物业费固定费用');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (3, 1, '', 'sanitation', '按户计费', 1, NULL, 15.00, NULL, '每户每月固定收取卫生费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '卫生费按户计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (4, 1, '', 'gas', '阶梯计费', 2, NULL, NULL, '[{\"end\": 28, \"price\": 2.61, \"start\": 0}, {\"end\": 42, \"price\": 2.87, \"start\": 28}, {\"end\": null, \"price\": 3.39, \"start\": 42}]', '按用气量阶梯计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '燃气费阶梯计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (5, 1, '', 'water', '按表计费', 3, 3.50, NULL, NULL, '按水表读数计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '水费按表计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (6, 1, '', 'electricity', '阶梯电价', 4, NULL, NULL, '[{\"end\": 170, \"price\": 0.477, \"start\": 0}, {\"end\": 260, \"price\": 0.527, \"start\": 170}, {\"end\": null, \"price\": 0.777, \"start\": 260}]', '按用电量阶梯计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '电费阶梯电价');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (7, 2, '', 'property', '按面积计费', 0, 3.00, NULL, NULL, '根据房屋面积和单价计算费用', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '物业费按面积计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (8, 2, '', 'property', '固定费用', 1, NULL, 250.00, NULL, '每户固定收取费用', 1, 2, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '物业费固定费用');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (9, 2, '', 'sanitation', '按户计费', 1, NULL, 20.00, NULL, '每户每月固定收取卫生费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '卫生费按户计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (10, 2, '', 'gas', '阶梯计费', 2, NULL, NULL, '[{\"end\": 30, \"price\": 2.8, \"start\": 0}, {\"end\": 45, \"price\": 3.1, \"start\": 30}, {\"end\": null, \"price\": 3.6, \"start\": 45}]', '按用气量阶梯计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '燃气费阶梯计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (11, 2, '', 'water', '按表计费', 3, 4.00, NULL, NULL, '按水表读数计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '水费按表计费');
INSERT INTO `sc_billing_method` (`id`, `property_company_id`, `building_type_name`, `fee_type`, `method_name`, `method_type`, `unit_price`, `fixed_amount`, `tier_config`, `description`, `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`, `deleted`, `version`, `remark`) VALUES (12, 2, '', 'electricity', '阶梯电价', 4, NULL, NULL, '[{\"end\": 180, \"price\": 0.5, \"start\": 0}, {\"end\": 280, \"price\": 0.55, \"start\": 180}, {\"end\": null, \"price\": 0.8, \"start\": 280}]', '按用电量阶梯计费', 1, 1, 1, '2025-08-14 09:51:22', 1, '2025-08-14 09:51:22', 0, 1, '电费阶梯电价');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
